package com.abc.db;

import java.util.ArrayList;
import java.util.List;

import com.abc.bean.Case;

import com.abc.bean.Text;

import com.abc.bean.Comment;
import com.abc.bean.Photo;
import com.abc.bean.Record;
import com.abc.bean.Template;

import com.abc.tool.StringTool;

import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBManager {
	private ExtDBHelper helper;
	private SQLiteDatabase db;
	
	public DBManager(Context context) {
		helper = new ExtDBHelper(context);
		//因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, mFactory);
		//所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
		db = helper.getWritableDatabase();
	}
	
	/**
	 * 新建一个案例
	 * @param caze
	 */
	public void addCase(Case caze) {
        db.beginTransaction();	//开始事务
        try {
        	db.execSQL("INSERT INTO caze VALUES(null, ?,?,?, ?, ?,?,?,?,?,?,?,?,?,?,?)", new Object[]{caze.getName(), caze.getAccidentDate(),caze.getAccidentAddress(),caze.getLastModifyDate(),caze.getCarNumber(),caze.getCarOwnerName(),caze.getCarOwnerPhone(),caze.getCarOwnerAddr(),caze.getInsuranceNumber(),caze.getNote(),caze.getOfficerName(),caze.getOfficerNumber(),caze.getImportant(),caze.getLocation(),caze.getCarBrand()});
        	db.setTransactionSuccessful();	//设置事务成功完成
        } finally {
        	db.endTransaction();	//结束事务
        }
	}
	
	/**
	 * 修改一个案例
	 * @param caze
	 */
	public void modifyCase(Case caze) {
		ContentValues cv = new ContentValues();
		cv.put("name", caze.getName());
		cv.put("accidentDate", caze.getAccidentDate());
		cv.put("accidentAddress", caze.getAccidentAddress());
		cv.put("lastModifyDate", caze.getLastModifyDate());
		cv.put("carNumber", caze.getCarNumber());
		cv.put("carOwnerName", caze.getCarOwnerName());
		cv.put("carOwnerPhone", caze.getCarOwnerPhone());
		cv.put("carOwnerAddr", caze.getCarOwnerAddr());
		cv.put("insuranceNumber", caze.getInsuranceNumber());
		cv.put("note", caze.getNote());
		cv.put("officerName", caze.getOfficerName());
		cv.put("officerNumber", caze.getOfficerNumber());
		cv.put("important", caze.getImportant());
		cv.put("location", caze.getLocation());
		cv.put("carBrand", caze.getCarBrand());
		db.beginTransaction();	//开始事务
        try {
        	db.update("caze", cv, "id = ?", new String[]{String.valueOf(caze.getId())});
        	db.setTransactionSuccessful();	//设置事务成功完成
        } finally {
        	db.endTransaction();	//结束事务
        }
	}
	
	/**
	 * 删除一个案例
	 * @param caze
	 */
	public void deleteCase(Case caze) {
		db.delete("caze", "id >= ?", new String[]{String.valueOf(caze.getId())});
	}
	
	/**
	 * query all persons, return list
	 * @return List<Person>
	 */
	public List<Case> queryCases() {
		List<Case> cases = new ArrayList<Case>();
		Cursor c = db.rawQuery("SELECT * FROM caze", null);
        while (c.moveToNext()) {
        	Case caze = new Case();
        	caze.setId(c.getInt(c.getColumnIndex("id")));
        	caze.setName(c.getString(c.getColumnIndex("name")));
        	caze.setAccidentAddress(c.getString(c.getColumnIndex("accidentAddress")));
        	caze.setAccidentDate(c.getString(c.getColumnIndex("accidentDate")));
        	caze.setLastModifyDate(c.getString(c.getColumnIndex("lastModifyDate")));
        	caze.setCarNumber(c.getString(c.getColumnIndex("carNumber")));
        	caze.setCarOwnerName(c.getString(c.getColumnIndex("carOwnerName")));
        	caze.setCarOwnerPhone(c.getString(c.getColumnIndex("carOwnerPhone")));
        	caze.setCarOwnerAddr(c.getString(c.getColumnIndex("carOwnerAddr")));
        	caze.setInsuranceNumber(c.getString(c.getColumnIndex("insuranceNumber")));
        	caze.setNote(c.getString(c.getColumnIndex("note")));
        	caze.setName(c.getString(c.getColumnIndex("name")));
        	caze.setOfficerName(c.getString(c.getColumnIndex("officerName")));
        	caze.setOfficerNumber(c.getString(c.getColumnIndex("officerNumber")));
        	caze.setCarBrand(c.getString(c.getColumnIndex("carBrand")));
        	caze.setLocation(c.getString(c.getColumnIndex("location")));
        	caze.setImportant(c.getString(c.getColumnIndex("important")));
        	cases.add(caze);
        }
        c.close();
        return cases;
	}
	
	
	public Case queryCase(Integer id){
		Case caze = new Case();
		Cursor c = db.rawQuery("select * from caze where id=?",new String[]{String.valueOf(id)});
		if(c.moveToFirst()) {			
        	caze.setId(c.getInt(c.getColumnIndex("id")));
        	caze.setName(c.getString(c.getColumnIndex("name")));
        	caze.setAccidentAddress(c.getString(c.getColumnIndex("accidentAddress")));
        	caze.setAccidentDate(c.getString(c.getColumnIndex("accidentDate")));
        	caze.setLastModifyDate(c.getString(c.getColumnIndex("lastModifyDate")));
        	caze.setCarNumber(c.getString(c.getColumnIndex("carNumber")));
        	caze.setCarOwnerName(c.getString(c.getColumnIndex("carOwnerName")));
        	caze.setCarOwnerPhone(c.getString(c.getColumnIndex("carOwnerPhone")));
        	caze.setCarOwnerAddr(c.getString(c.getColumnIndex("carOwnerAddr")));
        	caze.setInsuranceNumber(c.getString(c.getColumnIndex("insuranceNumber")));
        	caze.setNote(c.getString(c.getColumnIndex("note")));
        	caze.setName(c.getString(c.getColumnIndex("name")));
        	caze.setOfficerName(c.getString(c.getColumnIndex("officerName")));
        	caze.setImportant(c.getString(c.getColumnIndex("important")));
        	caze.setLocation(c.getString(c.getColumnIndex("location")));
        	caze.setCarBrand(c.getString(c.getColumnIndex("carBrand")));
        	caze.setOfficerNumber(c.getString(c.getColumnIndex("officerNumber")));
		}
		c.close();
		return caze;
	}
	
	/**
	 * 查询某个案例的照片数
	 * @param id
	 * @return
	 */
	public Integer countPhotos(Integer caseId){
		Cursor c = db.rawQuery("select count(*)from photo where caseId=?",new String[]{String.valueOf(caseId)});
		c.moveToFirst();
		Integer result = c.getInt(0);
		c.close();
		return result;
	}
	
	/**
	 * 查询某个案例的录音数
	 * @param id
	 * @return
	 */
	public Integer countRecords(Integer caseId){
		Cursor c = db.rawQuery("select count(*)from record where caseId=?",new String[]{String.valueOf(caseId)});
		c.moveToFirst();
		Integer result = c.getInt(0);
		c.close();
		return result;
	}
	
	/**
	 * 查询某个案例的文本数
	 * @param id
	 * @return
	 */
	public Integer countTexts(Integer caseId){
		Cursor c = db.rawQuery("select count(*)from text where caseId=?",new String[]{String.valueOf(caseId)});
		c.moveToFirst();
		Integer result = c.getInt(0);
		c.close();
		return result;
	}
	
	/***
	 * 查询对应案例的所有的文本记录
	 * @return
	 */
	public List<Text> queryTexts(Integer caseId){
		List<Text> texts = new ArrayList<Text>();
		Cursor c = db.query("text",new String[]{"id","title","content","lastModifyDate","caseId"},"caseId=?",new String[]{String.valueOf(caseId)},null,null,null);
        while (c.moveToNext()) {
        	Text text = new Text();
        	text.setId(c.getInt(c.getColumnIndex("id")));
        	text.setTitle(c.getString(c.getColumnIndex("title")));
        	text.setContent(c.getString(c.getColumnIndex("content")));
        	text.setLastModifyDate(c.getString(c.getColumnIndex("lastModifyDate")));
        	text.setCaseId(c.getInt(c.getColumnIndex("caseId")));
        	texts.add(text);
        }
        c.close();
        Log.d("sql","text count:"+texts.size()+"; caseId:caseId"+caseId);
        return texts;
	}
	
	public void modifyText(Text text){
		ContentValues cv = new ContentValues();
		cv.put("title", text.getTitle());
		cv.put("content", text.getContent());		
	    cv.put("lastModifyDate", text.getLastModifyDate());
	    cv.put("caseId", text.getCaseId());
		db.beginTransaction();	//开始事务
        try {
        	db.update("text", cv, "id = ?", new String[]{String.valueOf(text.getId())});
        	db.setTransactionSuccessful();	//设置事务成功完成
        } finally {
        	db.endTransaction();	//结束事务
        }
	}
	
	public Text queryText(Integer id){
		Text text = new Text();
		Cursor c = db.rawQuery("select * from text where id=?",new String[]{String.valueOf(id)});
		if(c.moveToFirst()) {			
        	text.setId(c.getInt(c.getColumnIndex("id")));
        	text.setTitle(c.getString(c.getColumnIndex("title")));
        	text.setContent(c.getString(c.getColumnIndex("content")));
        	text.setLastModifyDate(c.getString(c.getColumnIndex("lastModifyDate")));
        	text.setCaseId(c.getInt(c.getColumnIndex("caseId")));
		}
		c.close();
		return text;
	}
	
	public Integer addText(Text text){
		int id =0;
		//实例化常量值  
	    ContentValues cValue = new ContentValues();  
	    cValue.put("title",text.getTitle());  
	    cValue.put("content",text.getContent()); 
	    cValue.put("lastModifyDate", text.getLastModifyDate());
	    cValue.put("caseId", text.getCaseId());
	    
	    db.beginTransaction();	//开始事务
		try {
			id = (int) db.insert("text",null,cValue);  
			db.setTransactionSuccessful();  
		} finally {
        	db.endTransaction();	//结束事务
        }
		return id;
	}
	
	public Integer addTemplate(Template template){
		int id =0;
		//实例化常量值  
	    ContentValues cValue = new ContentValues();  
	    cValue.put("content",template.getContent());  	    
	    db.beginTransaction();	//开始事务
		try {
			id = (int) db.insert("template",null,cValue);  
			db.setTransactionSuccessful();  
		} finally {
        	db.endTransaction();	//结束事务
        }
		return id;
	}
	
	/***
	 * 查询所有的模板
	 * @return
	 */
	public List<Template> queryTemplates(){
		List<Template> templates = new ArrayList<Template>();
		Cursor c = db.rawQuery("SELECT * FROM template", null);
        while (c.moveToNext()) {
        	Template t = new Template();
        	t.setId(c.getInt(c.getColumnIndex("id")));
        	t.setContent(c.getString(c.getColumnIndex("content")));
        	
        	templates.add(t);
        }
        c.close();
        return templates;
	}
	/**

	 * 添加一个照片
	 */
	public void addPhoto(Photo photo) {
		db.beginTransaction();	//开始事务
		try {
        	db.execSQL("INSERT INTO photo VALUES(null, ?, ?, ?,?,?,?)", 
        			new Object[]{photo.getTitle(), photo.getDescription(), 
        			StringTool.date2String(photo.getDate(),"yyyy-MM-dd HH:mm:ss"), photo.getPath(), 
        			photo.getSize(), photo.getCaseId()});
        	db.setTransactionSuccessful();	//设置事务成功完成
        } finally {
        	db.endTransaction();	//结束事务
        }
	}
	
	/**
	 * 添加comment
	 */
	public void addComment(Comment comment) {
		db.beginTransaction();	//开始事务
		try {
        	db.execSQL("INSERT INTO comment VALUES(null, ?, ?, ?,?)", 
        			new Object[]{comment.getDate(), comment.getContent(), 
        			comment.getType(), comment.getCaseId()});
        	db.setTransactionSuccessful();	//设置事务成功完成
        } finally {
        	db.endTransaction();	//结束事务
        }
	}
	
	/**
	 * 修改一个照片
	 */
	public void modifyPhoto(Photo photo) {
		ContentValues cv = new ContentValues();
		System.out.println("photo.getTitle()="+photo.getTitle());
		cv.put("title", photo.getTitle());
		System.out.println("photo.getDescription()="+photo.getDescription());
		cv.put("description", photo.getDescription());
		cv.put("date", StringTool.date2String(photo.getDate(),"yyyy-MM-dd HH:mm:ss"));
		System.out.println("photo.getPath()="+photo.getPath());
		cv.put("path", photo.getPath());
		cv.put("size", photo.getSize());
		cv.put("caseId", photo.getCaseId());
		System.out.println("photo.getId()="+photo.getId());
		db.update("photo", cv, "id=?", new String[]{String.valueOf(photo.getId())});
		
	}
	
	/**
	 * 删除一个照片
	 */
	public void deletePhoto(Photo photo) {
		db.delete("photo", "id = ?", new String[]{String.valueOf(photo.getId())});
	}
	
	/**
	 * 查询照片
	 */
	public List<Photo> queryPhotos(Integer caseId) {
		List<Photo> photos = new ArrayList<Photo>();
		//Cursor c = db.rawQuery("SELECT * FROM photo", null);
		Cursor c = db.query("photo",new String[]{"id","title","description","date","path","size","caseId"},"caseId=?",new String[]{String.valueOf(caseId)},null,null,null);
		while(c.moveToNext())
		{
			Photo photo=new Photo();
			photo.setId(c.getInt(c.getColumnIndex("id")));
			photo.setTitle(c.getString(c.getColumnIndex("title")));
			photo.setDescription(c.getString(c.getColumnIndex("description")));
			photo.setDate(StringTool.string2Date(c.getString(c.getColumnIndex("date")),"yyyy-MM-dd HH:mm:ss"));
			photo.setPath(c.getString(c.getColumnIndex("path")));
			photo.setSize(c.getString(c.getColumnIndex("size")));
			photo.setCaseId(c.getInt(c.getColumnIndex("caseId")));
			photos.add(photo);
		}
		c.close();
		return photos;
	}
	
	/**
	 * 添加一个录音
	 */
	public void addRecord(Record record) {
		db.beginTransaction();	//开始事务
		try {
        	db.execSQL("INSERT INTO record VALUES(null, ?, ?, ?,?,?,?)", 
        			new Object[]{record.getTitle(), record.getDescription(), 
        			StringTool.date2String(record.getDate(),"yyyy-MM-dd HH:mm:ss"), record.getPath(), 
        			record.getLength(), record.getCaseId()});
        	db.setTransactionSuccessful();	//设置事务成功完成
        } finally {
        	db.endTransaction();	//结束事务
        }
	}
	
	/**
	 * 修改一个录音
	 */
	public void modifyRecord(Record record) {
		ContentValues cv = new ContentValues();
		cv.put("id", record.getId());
		cv.put("title", record.getTitle());
		cv.put("description", record.getDescription());
		cv.put("date", StringTool.date2String(record.getDate(),"yyyy-MM-dd HH:mm:ss"));
		cv.put("path", record.getPath());
		cv.put("length", record.getLength());
		//cv.put("caseId", record.getCaseId());
		db.update("record", cv, "id=?", new String[]{String.valueOf(record.getId())});
	}
	
	/**
	 * 删除一个录音
	 */
	public void deleteRecord(Record record) {
		db.delete("record", "id = ?", new String[]{String.valueOf(record.getId())});
	}
	
	/**
	 * 查询录音
	 */
	public List<Record> queryRecords() {
		List<Record> records=new ArrayList<Record>();
		Cursor c = db.rawQuery("SELECT * FROM record", null);
		while(c.moveToNext())
		{
			Record record=new Record();
			record.setId(c.getInt(c.getColumnIndex("id")));
			record.setTitle(c.getString(c.getColumnIndex("title")));
			record.setDescription(c.getString(c.getColumnIndex("description")));
			record.setDate(StringTool.string2Date(c.getString(c.getColumnIndex("date")),"yyyy-MM-dd HH:mm:ss"));
			record.setPath(c.getString(c.getColumnIndex("path")));
			record.setLength(c.getInt(c.getColumnIndex("length")));
			record.setCaseId(c.getInt(c.getColumnIndex("caseId")));
			records.add(record);
		}
		c.close();
		return records;
	}
	
	public List<Record> queryRecords(Integer caseId) {
		List<Record> records=new ArrayList<Record>();
		Cursor c = db.query("record",new String[]{"id","title","description","date","path","length","caseId"},"caseId=?",new String[]{String.valueOf(caseId)},null,null,null);
		while(c.moveToNext())
		{
			Record record=new Record();
			record.setId(c.getInt(c.getColumnIndex("id")));
			record.setTitle(c.getString(c.getColumnIndex("title")));
			record.setDescription(c.getString(c.getColumnIndex("description")));
			record.setDate(StringTool.string2Date(c.getString(c.getColumnIndex("date")),"yyyy-MM-dd HH:mm:ss"));
			record.setPath(c.getString(c.getColumnIndex("path")));
			record.setLength(c.getInt(c.getColumnIndex("length")));
			record.setCaseId(c.getInt(c.getColumnIndex("caseId")));
			records.add(record);
		}
		c.close();

		return records;
	}
	
	/**
	 * 删除一个文本
	 */
	public void deleteText(Integer id) {
		db.delete("text", "id = ?", new String[]{String.valueOf(id)});
	}

	/**
	 * 删除一个模板
	 */
	public void deleteTemplate(Integer id) {
		db.delete("template", "id = ?", new String[]{String.valueOf(id)});
	}
	
	/**
	 * 删除一个案例
	 */
	public void deleteCase(Integer id) {
		db.delete("caze", "id = ?", new String[]{String.valueOf(id)});
		db.delete("photo", "caseId = ?", new String[]{String.valueOf(id)});
		db.delete("record", "caseId = ?", new String[]{String.valueOf(id)});
		db.delete("text", "caseId = ?", new String[]{String.valueOf(id)});
		db.delete("comment", "caseId = ?", new String[]{String.valueOf(id)});		
	}
	
	/**
	 * close database
	 */
	public void closeDB() {
		db.close();
	}
}
